package com.andro.inje.chansung;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import com.andro.inje.chansung.bus.Bus;
import com.andro.inje.chansung.bus.BusDetail;
import com.andro.inje.chansung.department.Department;
import com.andro.inje.chansung.foodtable.Food;
import com.andro.inje.chansung.noticeboard.Notice;
import com.andro.inje.chansung.phonebook.Phone;
import com.andro.inje.chansung.proffesor.Proffesor;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DB {
	public static void createNoticeTable(SQLiteDatabase db) {
		String sql = "CREATE TABLE IF NOT EXISTS " + ConstantName.NOTICE_DB_TABLE_NAME + "(" +
					 "actualIndex INTEGER PRIMARY KEY," +
					 "hitNumber   INTEGER," +
					 "title       TEXT," +
					 "writer      TEXT," +
					 "date        TEXT," +
					 "link        TEXT);";
		db.execSQL(sql);
		
		sql       = "CREATE TABLE IF NOT EXISTS " + ConstantName.CAREER_DB_TABLE_NAME + "(" +
					 "actualIndex INTEGER PRIMARY KEY," +
					 "hitNumber   INTEGER," +
					 "title       TEXT," +
					 "writer      TEXT," +
					 "date        TEXT," +
					 "link        TEXT);";
		db.execSQL(sql);
		
		sql        = "CREATE TABLE IF NOT EXISTS " + ConstantName.FREE_DB_TABLE_NAME + "(" +
					 "actualIndex INTEGER PRIMARY KEY," +
					 "hitNumber   INTEGER," +
					 "title       TEXT," +
					 "writer      TEXT," +
					 "date        TEXT," +
					 "link        TEXT);";
		db.execSQL(sql);
		
		sql		    = "CREATE TABLE IF NOT EXISTS " + ConstantName.FOOD_MENU_TABLE_NAME + "(" +
					  "foodKind TEXT, " +
					  "foodTime TEXT, " +
					  "foodDay  TEXT, " +
					  "foodList TEXT);";
		db.execSQL(sql);
		
		sql			= "CREATE TABLE IF NOT EXISTS " + ConstantName.FOOD_A_DONG_MENU_TABLE_NAME + "(" +
					  "foodKind TEXT," +
					  "foodTime	TEXT," +
					  "foodDay	TEXT," +
					  "foodList	TEXT);";
		db.execSQL(sql);
		
		sql			= "CREATE TABLE IF NOT EXISTS " + ConstantName.FOOD_INDUK_MENU_TABLE_NAME + "(" +
					  "foodTime TEXT," +
					  "foodDay  TEXT," +
					  "foodList	TEXT);";
		db.execSQL(sql);
		
		sql			= "CREATE TABLE IF NOT EXISTS " + ConstantName.FOOD_INJUNG_MENU_TABLE_NAME + "(" +
					  "foodTime TEXT," +
					  "foodDay	TEXT," +
					  "foodList	TEXT);";
		db.execSQL(sql);
		
		sql			= "CREATE TABLE IF NOT EXISTS " + ConstantName.DEPARTMENT_INTRO_TABLE_NAME + "(" +
					  "cheifName			TEXT," +
					  "phoneNumber		TEXT," +
					  "faxNumber			TEXT," +
					  "officeLocation	TEXT," +
					  "introduction		TEXT," +
					  "feature			TEXT," +
					  "learningSubject	TEXT," +
					  "jobRelated		TEXT);";
		db.execSQL(sql);
		
		sql			= "CREATE TABLE IF NOT EXISTS " + ConstantName.DEPARTMENT_PROFFESOR_INTRO_TABLE_NAME + "(" +
					  "name		TEXT," +
					  "degree	TEXT," +
					  "major		TEXT," +
					  "lab		TEXT," +
					  "email		TEXT," +
					  "imageUrl	TEXT);";
		db.execSQL(sql);
		

	}
	
	public static void createBusDetailTables(SQLiteDatabase db) {
		String sql = "CREATE TABLE IF NOT EXISTS " + ConstantName.BUS_DETAIL_INFO + "(" +
				     "destination	TEXT," +
				     "time			TEXT," +
				     "attention		TEXT," +
				     "number			INTEGER," +
				     "description	TEXT," +
				     "isComeOrLeave	TEXT);";
		db.execSQL(sql);
	}
	
	public static void removeBusDetailTables(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.BUS_DETAIL_INFO);
	}
	
	public static void insertDetailDataIntoBusTable(SQLiteDatabase db, BusDetail bus) {
		String sql = "INSERT INTO " + ConstantName.BUS_DETAIL_INFO + "(destination, time, attention, number, description, isComeOrLeave)" +
					 "VALUES	('"+ bus.getDestination() + "', '"
					 		  + bus.getTime() + "', '" 
					 		  + bus.getAttention() + "', " 
					 		  + bus.getNumber() + ", '" 
					 		  + bus.getDescription() + "', '" 
					 		  +	bus.isComeOrLeave()	+ "');";
		db.execSQL(sql);
	}
	
	public static List<BusDetail> getDetailBusDataFromBusTableByName(SQLiteDatabase db, String name) {
		String sql = "SELECT number, destination, time, attention , description, isComeOrLeave " +
					 "FROM " + ConstantName.BUS_DETAIL_INFO + " " +
					 "WHERE destination = '" + name + "';";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		List<BusDetail> busList = new ArrayList<BusDetail>();
		
		for( int i = 0; i < cursor.getCount(); i++ ) {
			BusDetail bus = new BusDetail(cursor.getInt(0),
										 cursor.getString(1),
										 cursor.getString(2),
										 cursor.getString(3), 
										 cursor.getString(4), 
										 Boolean.parseBoolean(cursor.getString(5)));
			busList.add(bus);
			cursor.moveToNext();
		}
		
		return busList;
	}
	
	public static void removeBusTables(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.BUS_BASIC_INFO);
	}
	
	public static void createBusTables(SQLiteDatabase db) {
		String sql = "CREATE TABLE IF NOT EXISTS " + ConstantName.BUS_BASIC_INFO + "(" +
					 "destination	TEXT		PRIMARY KEY," +
					 "ticket			TEXT	," +
					 "card			TEXT);";
		db.execSQL(sql);
	}
	
	public static void insertBasicDataIntoBusTable(SQLiteDatabase db, Bus bus) {
		String sql = "INSERT INTO " + ConstantName.BUS_BASIC_INFO + "(destination, ticket, card) " +
					 "VALUES('" + bus.getDestination() + "', '" 
					 		   + bus.getTicketFare() + "', '" 
					 		   +	 bus.getCardFare() + "');";
		db.execSQL(sql);
	}
	
	public static List<Bus> getAllBusLists(SQLiteDatabase db) {
		String sql = "SELECT destination, ticket, card " +
					 "FROM " + ConstantName.BUS_BASIC_INFO + ";";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		List<Bus> buses = new ArrayList<Bus>();
		
		for( int i = 0; i < cursor.getCount(); i++ ) {
			Bus bus = new Bus(cursor.getString(0), cursor.getString(1), cursor.getString(2));
			buses.add(bus);
			cursor.moveToNext();
		}
		
		return buses;
	}
	
	public static void createPhoneTables(SQLiteDatabase db) {
		String sql 	= "CREATE TABLE IF NOT EXISTS " + ConstantName.PHONE_BOOK_OUTER_TABLE + "(" +
				  	  "phoneOuterParty TEXT PRIMARY KEY);";
		db.execSQL(sql);
	
		sql			= "CREATE TABLE IF NOT EXISTS " + ConstantName.PHONE_BOOK_INNER_TABLE + "(" +
					  "phoneOuterParty		TEXT," +
					  "phoneInnerParty		TEXT," +
					  "phoneNumber			TEXT," +
					  "faxNumber				TEXT);";
		db.execSQL(sql);		
	}
	
	public static void insertDataIntoPhoneOuter(SQLiteDatabase db, String headName) {
		String sql = "INSERT INTO " + ConstantName.PHONE_BOOK_OUTER_TABLE + " " +
				     "VALUES('" + headName + "');";
		db.execSQL(sql);
	}
	
	public static boolean doesFoodTableExist(SQLiteDatabase db) {
		String sql = "SELECT * FROM " + ConstantName.FOOD_MENU_TABLE_NAME + ";";
		
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		if( cursor.getCount() >= 1 ) {
			return true;
		} 
		return false;
	}
	
	public static void removeNoticeTable(SQLiteDatabase db) {
		removeNoticeTables(db);
		removeFoodTables(db);
		removeDepartmentTable(db);
		removeDepartmentProffesorTable(db);
	}
	
	public static void removePhoneBookTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.PHONE_BOOK_OUTER_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.PHONE_BOOK_INNER_TABLE);
	}
	
	public static void removeDepartmentTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.DEPARTMENT_INTRO_TABLE_NAME);
	}
	
	public static void removeDepartmentProffesorTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.DEPARTMENT_PROFFESOR_INTRO_TABLE_NAME);
	}
	
	public static void removeNoticeTables(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.NOTICE_DB_TABLE_NAME + ";");
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.CAREER_DB_TABLE_NAME + ";");
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.FREE_DB_TABLE_NAME + ";");		
	}
	
	public static void removeFoodTables(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.FOOD_MENU_TABLE_NAME + ";");
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.FOOD_INDUK_MENU_TABLE_NAME + ";");
		db.execSQL("DROP TABLE IF EXISTS " + ConstantName.FOOD_INJUNG_MENU_TABLE_NAME + ";");		
	}
	
	public static void deleteDatasInFoodTables(SQLiteDatabase db) {
		db.execSQL("DELETE * FROM " + ConstantName.FOOD_MENU_TABLE_NAME + ";");
		db.execSQL("DELETE * FROM " + ConstantName.FOOD_INDUK_MENU_TABLE_NAME + ";");
		db.execSQL("DELETE * FROM " + ConstantName.FOOD_INJUNG_MENU_TABLE_NAME + ";");
	}
	
	public static void insertDataIntoPhoneBookTable(SQLiteDatabase db, Phone phone) {
		String sql = "SELECT phoneOuterParty " +
				     "FROM " + ConstantName.PHONE_BOOK_OUTER_TABLE + " " +
				     "WHERE phoneOuterParty = '" + phone.getHeadName() + "';";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		
		if( cursor.getCount() == 0 ) {
			sql		   = "INSERT INTO " + ConstantName.PHONE_BOOK_OUTER_TABLE + 
						 "(phoneOuterParty) " +
						 "VALUES('"+ phone.getHeadName() +"');";
			db.execSQL(sql);
		}
		cursor.close();
		
		for( String key : phone.getPhoneNumbers().keySet() ) {
			sql			= "INSERT INTO " + ConstantName.PHONE_BOOK_INNER_TABLE +
						  "(phoneOuterParty, phoneInnerParty, phoneNumber, faxNumber) " +
						  "VALUES('" + phone.getHeadName() + "', '"
						  	 	     + key + "', '"
						  		     + phone.getPhoneNumbers().get(key) + "', '"
						  		     + phone.getFaxNumbers().get(key) + "');";
			db.execSQL(sql);
		}
	}
	
	public static List<String> getHeadNamesFromPhoneBookTable(SQLiteDatabase db) {
		String sql = "SELECT phoneOuterParty " +
					 "FROM " + ConstantName.PHONE_BOOK_OUTER_TABLE + ";";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		List<String> headNameList = new ArrayList<String>();
		
		for( int i = 0; i < cursor.getCount(); i++ ) {
			headNameList.add(cursor.getString(0));
			cursor.moveToNext();
		}
		cursor.close();
		
		return headNameList;
	}
	
	public static Phone getDataFromPhoneBookTableByItsHeadName(SQLiteDatabase db, String headName) {
		String sql = "SELECT phoneInnerParty, phoneNumber, faxNumber " +
				     "FROM " + ConstantName.PHONE_BOOK_INNER_TABLE + " " +
				     "WHERE phoneOuterParty = '" + headName + "';";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		Map<String, String> phoneNumbers	= new TreeMap<String, String>();
		Map<String, String> faxNumbers	= new TreeMap<String, String>();
		Phone phone = null;
		
		for( int i = 0; i < cursor.getCount(); i++ ) {
			phoneNumbers.put(cursor.getString(0), cursor.getString(1));
			faxNumbers.put(cursor.getString(0), cursor.getString(2));
			cursor.moveToNext();
		}
		phone = new Phone(headName, phoneNumbers, faxNumbers);
		return phone;
	}
	
	public static void insertDataIntoProffesorTable(SQLiteDatabase db, Proffesor proffesor) {
		String sql = "INSERT INTO " + ConstantName.DEPARTMENT_PROFFESOR_INTRO_TABLE_NAME +
					 "(name, degree, major, lab, email, imageUrl) " +
					 "VALUES('" + proffesor.getName() + "', '"
					 		   + proffesor.getDegree() + "', '"
					 		   + proffesor.getMajor() + "', '"
					 		   + proffesor.getLab() + "', '"
					 		   + proffesor.getEmail() + "', '" 
					 		   + proffesor.getImageUrlAdr() + "');";
		db.execSQL(sql);
	}
	
	public static List<Proffesor> getDataFromProffesorTable(SQLiteDatabase db) {
		String sql = "SELECT name, degree, major, lab, email, imageUrl " +
					 "FROM " + ConstantName.DEPARTMENT_PROFFESOR_INTRO_TABLE_NAME +";";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		List<Proffesor> proffesors = new ArrayList<Proffesor>();
		for( int i = 0; i < cursor.getCount(); i++ ) {
			Proffesor proffesor = new Proffesor(cursor.getString(0), 
											   cursor.getString(1),
											   cursor.getString(2), 
											   cursor.getString(3),
											   cursor.getString(4),
											   cursor.getString(5));
			proffesors.add(proffesor);
			cursor.moveToNext();
		}
		cursor.close();
		
		return proffesors;
	}
	
	public static void insertDataIntoDepartmentTable(SQLiteDatabase db, Department department) {
		String sql = "INSERT INTO " + ConstantName.DEPARTMENT_INTRO_TABLE_NAME + 
				     "(cheifName, phoneNumber, faxNumber, officeLocation, introduction, feature, learningSubject, jobRelated) " +
				     "VALUES('" + department.getCheifName() + "', '"
				     		   + department.getPhoneNumber() + "', '"
				     		   + department.getFaxNumber() + "', '"
				     		   + department.getOfficeLocation() + "', '"
				     		   + department.getIntroduction() + "', '"
				     		   + department.getFeature() + "', '"
				     		   + department.getLearningSubject() + "', '"
				     		   + department.getJobRelated() + "');";
		db.execSQL(sql);
	}
	
	public static Department getDataFromDepartmentTable(SQLiteDatabase db) {
		String sql = "SELECT * " +
					 "FROM " + ConstantName.DEPARTMENT_INTRO_TABLE_NAME + ";";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		Department department = new Department(cursor.getString(0), 
											  cursor.getString(1), 
											  cursor.getString(2),
											  cursor.getString(3),
											  cursor.getString(4),
											  cursor.getString(5),
											  cursor.getString(6),
											  cursor.getString(7));
		
		return department;
	}

	public static void insertDataIntoDainOrADongOrBioFoodTable(SQLiteDatabase db, boolean isADong, boolean isBioTech, Food food) {
		if( isADong ) {
			insertDataIntoFoodTable(db, ConstantName.FOOD_A_DONG_MENU_TABLE_NAME, food);
		} else if( isBioTech ){
			insertDataIntoInjungFoodTable(db, food);
		} else {
			insertDataIntoFoodTable(db, ConstantName.FOOD_MENU_TABLE_NAME, food);
		}
	}
	
	private static void insertDataIntoFoodTable(SQLiteDatabase db, String tableName, Food food) {
		String sql = "INSERT INTO " + tableName + "(foodKind, foodTime, foodDay, foodList) " +
					 "VALUES('" + food.getKind() + "', '" 
					 		   + food.getTime() + "', '" 
					 		   + food.getDay() + "', '" 
					 		   + food.getMenu() + "');";
		db.execSQL(sql);
	}
	
	public static void insertDataIntoIndukOrInjungFoodTable(SQLiteDatabase db, String kind, Food food) {
		if( kind.equals(ConstantName.FOOD_INDUK_MENU_TABLE_NAME) ) {
			insertDataIntoIndukFoodTable(db, food);
		} else {
			insertDataIntoInjungFoodTable(db, food);
		}
	}
	
	private static void insertDataIntoIndukFoodTable(SQLiteDatabase db, Food food) {
		String sql = "INSERT INTO " + ConstantName.FOOD_INDUK_MENU_TABLE_NAME + "(foodTime, foodDay, foodList) " +
					 "VALUES('" + food.getTime() + "', '"
					 		   + food.getDay()  + "', '"
					 		   + food.getMenu() + "');";
		db.execSQL(sql);
	}
	
	private static void insertDataIntoInjungFoodTable(SQLiteDatabase db, Food food) {
		String sql = "INSERT INTO " + ConstantName.FOOD_INJUNG_MENU_TABLE_NAME + "(foodTime, foodDay, foodList) " +
				 "VALUES('" + food.getTime() + "', '"
				 		   + food.getDay()  + "', '"
				 		   + food.getMenu() + "');";
		db.execSQL(sql);
	}
	
	public static List<Food> getMenuFromInjungFoodTableByItsDayAndTime(SQLiteDatabase db, ConstantId.FOOD_DAY day, ConstantId.FOOD_TIME time) {
		String sql = "SELECT * " +
					 "FROM " + ConstantName.FOOD_INJUNG_MENU_TABLE_NAME + " " +
					 "WHERE foodDay  = '" + day + "' AND " +
					 " 	    foodTime = '" + time + "';";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		List<Food> foodList = new ArrayList<Food>();
		
		for( int i = 0; i < cursor.getCount(); i++ ) {
			Food food = new Food(ConstantId.FOOD_KIND.A, 
								 ConstantId.FOOD_TIME.valueOf(cursor.getString(0)), 
								 ConstantId.FOOD_DAY.valueOf(cursor.getString(1)), 
								 cursor.getString(2));
			foodList.add(food);
			cursor.moveToNext();
		}
		cursor.close();
		
		return foodList;
	}
	
	public static List<Food> getMenuFromIndukFoodTableByItsDayAndTime(SQLiteDatabase db, ConstantId.FOOD_DAY day, ConstantId.FOOD_TIME time) {
		String sql = "SELECT * " +
					 "FROM " + ConstantName.FOOD_INDUK_MENU_TABLE_NAME + " " +
					 "WHERE foodDay  = '" + day + "' AND " +
					 " 	    foodTime = '" + time + "';";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		List<Food> foodList = new ArrayList<Food>();
		
		for( int i = 0; i < cursor.getCount(); i++ ) {
			Food food = new Food(ConstantId.FOOD_KIND.A, 
								 ConstantId.FOOD_TIME.valueOf(cursor.getString(0)), 
								 ConstantId.FOOD_DAY.valueOf(cursor.getString(1)), 
								 cursor.getString(2));
			foodList.add(food);
			cursor.moveToNext();
		}
		cursor.close();
		
		return foodList;
	}
	
	public static List<Food> getMenuFromDainOrADongFoodTableByItsDayAndTime(SQLiteDatabase db, boolean isADong, ConstantId.FOOD_DAY day, ConstantId.FOOD_TIME time) {
		if( isADong ) {
			return 	getMenuFromFoodTableByItsDayAndTime(db, ConstantName.FOOD_A_DONG_MENU_TABLE_NAME, day, time);
		} 
		return getMenuFromFoodTableByItsDayAndTime(db, ConstantName.FOOD_MENU_TABLE_NAME, day, time);
	}
	
	private static List<Food> getMenuFromFoodTableByItsDayAndTime(SQLiteDatabase db, String tableName, ConstantId.FOOD_DAY day, ConstantId.FOOD_TIME time) {
		String sql = "SELECT * " +
					 "FROM " + tableName + " " +
					 "WHERE foodDay  = '" + day + "' AND " +
					 " 	    foodTime = '" + time + "';";
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		List<Food> foodList = new ArrayList<Food>();
		
		for( int i = 0; i < cursor.getCount(); i++ ) {
			Food food = new Food(ConstantId.FOOD_KIND.valueOf(cursor.getString(0)), 
								ConstantId.FOOD_TIME.valueOf(cursor.getString(1)), 
								ConstantId.FOOD_DAY.valueOf(cursor.getString(2)), 
								cursor.getString(3));
			foodList.add(food);
			cursor.moveToNext();
		}
		cursor.close();
		
		return foodList;
	}
	
	public static void insertDataIntoFreeTable(SQLiteDatabase db, Notice notice) {
		inserDataIntoBoardTable(db, ConstantName.FREE_DB_TABLE_NAME, notice);
	}
	
	public static void insertDataIntoCareerTable(SQLiteDatabase db, Notice notice) {
		inserDataIntoBoardTable(db, ConstantName.CAREER_DB_TABLE_NAME, notice);
	}
	
	public static void insertDataIntoNoticeTable(SQLiteDatabase db, Notice notice) {
		inserDataIntoBoardTable(db, ConstantName.NOTICE_DB_TABLE_NAME, notice);
	}
	
	private static void inserDataIntoBoardTable(SQLiteDatabase db, String tableKind, Notice notice) {
		String checkSql = "SELECT actualIndex " +
				  "FROM " + tableKind + " " +
				  "WHERE actualIndex = " + notice.getActualIndex() + ";";
		Cursor cursor = db.rawQuery(checkSql, null);
		cursor.moveToNext();
		
		if( cursor.getCount() == 0 ) {
			String sql = "INSERT INTO " + tableKind + "(actualIndex, hitNumber, title, writer, date, link) " +
						 "VALUES("+ notice.getActualIndex() +", "
						 		  + notice.getHitNumber() + ", "
						 		  + "'" + notice.getTitle() + "', "
						 		  + "'" + notice.getWriter() + "', "
						 		  + "'" + notice.getDate() + "', "
						 		  + "'" + notice.getLink() + "');";
			
			db.execSQL(sql);
		} else {
			Log.d("DB", "the table already exists");
		}
	}
	
	public static List<Notice> getFreeFromFreeTable(SQLiteDatabase db) {
		return getBoardListFromTable(db, ConstantName.FREE_DB_TABLE_NAME);
	}
	
	public static List<Notice> getCareerFromCareerTable(SQLiteDatabase db) {
		return getBoardListFromTable(db, ConstantName.CAREER_DB_TABLE_NAME);
	}
	
	public static List<Notice> getNoticeFromNoticeTable(SQLiteDatabase db) {
		return getBoardListFromTable(db, ConstantName.NOTICE_DB_TABLE_NAME);
	}
	
	private static List<Notice> getBoardListFromTable(SQLiteDatabase db, String tableKind) {
		List<Notice> notices = new ArrayList<Notice>();
		
		String sql = "SELECT actualIndex, hitNumber, title, writer, date, link " +
					 "FROM "+ tableKind + ";";
		
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToNext();
		
		for( int i = 0; i < cursor.getCount(); i++ ) {
			Notice notice = new Notice(0, 
									  cursor.getInt(0), 
									  cursor.getInt(1), 
									  cursor.getString(2), 
									  cursor.getString(3), 
									  cursor.getString(4), 
									  cursor.getString(5));
			notices.add(notice);
			cursor.moveToNext();
		}
		cursor.close();
		
		Collections.sort(notices);
		Collections.reverse(notices);
		
		Notice firstNotice  = notices.remove(0);
		Notice secondNotice = notices.remove(0);
		Notice thirdNotice  = notices.remove(0);
		
		firstNotice.setNew(true);
		secondNotice.setNew(true);
		thirdNotice.setNew(true);
		
		notices.add(0, thirdNotice);
		notices.add(0, secondNotice);
		notices.add(0, firstNotice);
		
		return notices;
	}
}
